package com.aiflow.Action;


import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.struts2.ServletActionContext;

import com.aiflow.db.ConnDB;
import com.aiflow.util.DownExcell;

import net.sf.json.JSONArray;
import net.sf.json.JSONObject;


//  告警查询 
public class SendRecordAction {

	
	
	// 查询 告警信息
	public void findAlarmRecord() {
		HttpServletRequest req = ServletActionContext.getRequest();
		int start = Integer.parseInt(req.getParameter("start"));
		int limit = Integer.parseInt(req.getParameter("limit"));
		int end = start + limit;
		int begin = start + 1;
		String startTime= (String) req.getParameter("startTime");
		String endTime = (String) req.getParameter("endTime");
		String backVal = (String) req.getParameter("backCom");  //返回值
		if(startTime != null && !startTime.equals("")){
		startTime = (startTime.split("T"))[0];
		}
		if(endTime != null && !endTime.equals("")){
		endTime = (endTime.split("T"))[0];
		}

		ConnDB db = new ConnDB();
		ResultSet rs = null;
		JSONObject json = new JSONObject();
		List<Map<String, String>> datalist = new ArrayList<Map<String, String>>();
		JSONArray datas = new JSONArray();
		String whereSql = "";
		if(startTime != null && !startTime.equals("")){
		    whereSql += " and   sendTime >= '"+startTime+"' ";
		 }
		if(endTime != null && !endTime.equals("")){
			whereSql += " and  sendTime < '"+endTime+"' ";
		}
		if(backVal != null && !backVal.equals("")){
			whereSql += " and  disposition = '"+backVal+"' ";
		}
		
		String sqlCount = "";
		Connection con = db.getConnection();
		PreparedStatement ps = null;

		try {
			String sql = "select * from "
					+ "("
					+ " select a.id,alarmId,MVB_CALLER,u.user_name,tel,a.alarmTargetId,d.name,sendTime,backTime, case disposition when 'ANSWERED' then '已接听' when '' then '正在接通' else '未接听' end as disposition ,"+  
                      " (row_number() over(ORDER BY a.id desc)) rb,snumbe  from t_s_alarmRecord a,dbo.t_s_userConfigure u,dictionary d "+
                      " where a.userId = u.id and a.alarmTargetId = d.id "+whereSql+") tt "
					+ "where tt.rb between " + begin + " and " + end;

			sqlCount = "select count(1) "+
					  " from t_s_alarmRecord a,dbo.t_s_userConfigure u,dictionary d where a.userId = u.id and a.alarmTargetId = d.id "+whereSql;   
			ps = con.prepareStatement(sql);
			rs = ps.executeQuery();

			while (rs.next()) {
				Map<String, String> mapdata = new HashMap<String, String>();
				mapdata.put("id", rs.getString(1));
				mapdata.put("alarmId", rs.getString(2));
				mapdata.put("MVB_CALLER", rs.getString(3));
				mapdata.put("userName", rs.getString(4));
				mapdata.put("tel", rs.getString(5));
				mapdata.put("deptId", rs.getString(7));
				mapdata.put("sendTime", rs.getString(8));
				mapdata.put("backTime", rs.getString(9));
				mapdata.put("disposition", rs.getString(10));
				mapdata.put("snumbe", rs.getString(12));
				datalist.add(mapdata);
			}
			int index = db.GetInfoCount(sqlCount);
			datas = JSONArray.fromObject(datalist);
			json.put("success", true);
			json.put("datas", datas);
			json.put("results", index);
			HttpServletResponse response = ServletActionContext.getResponse();
			response.setCharacterEncoding("UTF-8");
			PrintWriter out = response.getWriter();
			out.print(json);
			out.close();

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				rs.close();
				ps.close();
				con.close();
			} catch (Exception ex1) {
				ex1.printStackTrace();
			}
		}
	}
	
	  public void down(){
			HttpServletRequest req = ServletActionContext.getRequest();
			HttpServletResponse res = ServletActionContext.getResponse();
			String startTime= (String) req.getParameter("startTime");
			String endTime = (String) req.getParameter("endTime");
			String backVal = (String) req.getParameter("backCom");  //返回值
			if(startTime != null && !startTime.equals("")){
			startTime = (startTime.split("T"))[0];
			}
			if(endTime != null && !endTime.equals("")){
			endTime = (endTime.split("T"))[0];
			}
			String whereSql = "";
			if(startTime != null && !startTime.equals("")){
			    whereSql += " and   sendTime >= '"+startTime+"' ";
			 }
			if(endTime != null && !endTime.equals("")){
				whereSql += " and  sendTime < '"+endTime+"' ";
			}
			if(backVal != null && !backVal.equals("")){
				whereSql += " and  disposition = '"+backVal+"' ";
			}
				String sql =" select  alarmId 告警编号 ,MVB_CALLER,u.user_name 接受人,tel 手机 ,d.name 告警对象,sendTime 发送时间,backTime 返回时间 ,case disposition when 'ANSWERED' then '已接听' when '' then '正在接通' else '未接听' end as 发送状态  "+  
	                      "   from t_s_alarmRecord a,dbo.t_s_userConfigure u,dictionary d "+
	                      " where a.userId = u.id and a.alarmTargetId = d.id "+whereSql;
				DownExcell excel = new DownExcell();
				excel.getData(sql, req, res,"语音通知");
  	  
	  }
	  
	  //导出工单语音通知的Excel
	  public void superviseRecorddown(){
			HttpServletRequest req = ServletActionContext.getRequest();
			HttpServletResponse res = ServletActionContext.getResponse();
			String region = req.getParameter("region");
			String startTime= (String) req.getParameter("startTime");
			String endTime = (String) req.getParameter("endTime");
			String backVal = (String) req.getParameter("backCom");  //返回值
			if(startTime != null && !startTime.equals("")){
			startTime = (startTime.split("T"))[0];
			}
			if(endTime != null && !endTime.equals("")){
			endTime = (endTime.split("T"))[0];
			}
			String whereSql = "";
			if(startTime != null && !startTime.equals("")){
			    whereSql += " and   sendTime >= '"+startTime+"' ";
			 }
			if(endTime != null && !endTime.equals("")){
				whereSql += " and  sendTime < '"+endTime+"' ";
			}
			if(backVal != null && !backVal.equals("")){
				whereSql += " and  disposition = '"+backVal+"' ";
			}
			if (region != null && !region.equals("")) {
				whereSql += " and   region_name like '%" + region + "%' ";
			}
				String sql =" select superviseId 工单编号 ,MVB_CALLER,a.user_name 接受人,tel 手机 ,d.name 派单对象,sendTime 发送时间,backTime 返回时间 ,case disposition when 'ANSWERED' then '已接听' when '' then '正在接通' else '未接听' end as 发送状态,context as 通话内容,case supervise_status when 770 then '受理' when 771 then '回复' when 773 then '实时督办' else '过程' end as 工单状态,region_name as 地市  "+  
	                      "   from t_s_superviseRecord a,dictionary d "+
	                      " where a.superviseTargetId = d.id "+whereSql+
	                      " order by 发送时间";
				DownExcell excel = new DownExcell();
				excel.getData(sql, req, res,"工单语音通知");
	  
	  }
	  
	/**
	 * @param args
	 */
	public static void main(String[] args) {
		// TODO Auto-generated method stub

	}

}
